const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const bcrypt = require('bcrypt');

// 数据库文件路径
const dbPath = path.resolve(__dirname, 'chat.db');

// 创建数据库连接
let db = new sqlite3.Database(dbPath, (err) => {
    if (err) {
        console.error('Could not connect to database:', err.message);
    } else {
        console.log('Connected to SQLite database.');
        initDatabase();
    }
});

// 初始化数据库
function initDatabase() {
    // 创建用户表
    db.run(`
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            is_admin BOOLEAN NOT NULL DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    `, (err) => {
        if (err) {
            console.error('Error creating users table:', err.message);
        } else {
            console.log('Users table created or already exists.');
            // 创建消息表
            db.run(`
                CREATE TABLE IF NOT EXISTS messages (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    sender_id INTEGER NOT NULL,
                    receiver_id INTEGER NOT NULL,
                    content TEXT,
                    message_type TEXT DEFAULT 'text',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (sender_id) REFERENCES users (id),
                    FOREIGN KEY (receiver_id) REFERENCES users (id)
                )
            `, (err) => {
                if (err) {
                    console.error('Error creating messages table:', err.message);
                } else {
                    console.log('Messages table created or already exists.');
                    // 检查是否已有管理员账户
                    checkAdminUser();
                }
            });
        }
    });
}

// 检查并创建管理员账户
function checkAdminUser() {
    db.get(`SELECT id FROM users WHERE is_admin = 1`, (err, row) => {
        if (err) {
            console.error('Error checking admin user:', err.message);
        } else if (!row) {
            // 创建默认管理员账户
            const hashedPassword = bcrypt.hashSync('admin123', 10);
            db.run(
                `INSERT INTO users (username, password, is_admin) VALUES (?, ?, ?)`,
                ['admin', hashedPassword, 1],
                (err) => {
                    if (err) {
                        console.error('Error creating admin user:', err.message);
                    } else {
                        console.log('Default admin user created: username=admin, password=admin123');
                    }
                }
            );
        }
    });
}

module.exports = db;